from support.map.region import Region
import mariadb,os,sys

class SQLHelper:
    TABLE_RESOLD_HOUSE= "resold_house_"
        
    def __init__(self, database, city, hostaddr, username, pwd):
        try:
            self.conn = mariadb.connect(
                user=username,
                password=pwd,
                host=hostaddr,
                port=3306,
                database=database)
        except mariadb.Error as e:
            print("Error connecting to MariaDB Platform: {}".format(str(e)))
            sys.exit(1)
        cur = self.conn.cursor()
        cur.execute("CREATE TABLE IF NOT EXISTS {}{} (ID INTEGER PRIMARY KEY AUTO_INCREMENT, REGION VARCHAR(100), MAXPRICE FLOAT, TOTAL FLOAT, COUNT INT, LONGITUDE FLOAT, LATITUDE FLOAT);".format(SQLHelper.TABLE_RESOLD_HOUSE, city))
        cur.close()
        self.city = city
    
    def __insertRegion(self, region):
        cur = self.conn.cursor()
        cur.execute("INSERT INTO {}{} (REGION, MAXPRICE, TOTAL, COUNT) VALUES ('{}', 0, 0, 0);".format(SQLHelper.TABLE_RESOLD_HOUSE, self.city, region))
        rowid = cur.lastrowid
        cur.close()
        return rowid

    def __updateRegionInfo(self, id, region, max, total, count):
        cur = self.conn.cursor()
        location = Region.translatAddr(self.city, region)
        if(location):
            cur.execute("UPDATE {}{} SET MAXPRICE={}, TOTAL={}, COUNT={}, LONGITUDE={}, LATITUDE={} WHERE ID={};".format(SQLHelper.TABLE_RESOLD_HOUSE, self.city, max, total, count, float(location[0]), float(location[1]), id))
        else:
            cur.execute("UPDATE {}{} SET MAXPRICE={}, TOTAL={}, COUNT={} WHERE ID={};".format(SQLHelper.TABLE_RESOLD_HOUSE, self.city, max, total, count, id))
        self.conn.commit()
        cur.close()

    def __getRegionInfo(self, region):
        cur = self.conn.cursor()
        cur.execute("SELECT ID, MAXPRICE, TOTAL, COUNT FROM {}{} WHERE REGION=?;".format(SQLHelper.TABLE_RESOLD_HOUSE, self.city), (region,))
        info = cur.fetchone()
        cur.close()
        return info

    def __createRegionTable(self, region):
        cur = self.conn.cursor()
        cur.execute("CREATE TABLE IF NOT EXISTS {}{}_{} (ID INTEGER PRIMARY KEY AUTO_INCREMENT, HOUSE VARCHAR(100), AREA VARCHAR(200), HEIGHT VARCHAR(200), PRICE FLOAT, LONGITUDE FLOAT, LATITUDE FLOAT);".format(SQLHelper.TABLE_RESOLD_HOUSE, self.city, region))
        cur.close()

    def __addNewHouseInfo(self, region, house, area, height, price):
        location = Region.translatAddr(self.city, region, house)
        cur = self.conn.cursor()
        if(location):
            cur.execute("INSERT INTO {}{}_{} (HOUSE, AREA, HEIGHT, PRICE, LONGITUDE, LATITUDE) VALUES (?, ?, ?, ?, ?, ?);".format(SQLHelper.TABLE_RESOLD_HOUSE, self.city, region), (house, area, height, price, float(location[0]), float(location[1])))
        else:
            cur.execute("INSERT INTO {}{}_{} (HOUSE, AREA, HEIGHT, PRICE) VALUES (?, ?, ?, ?);".format(SQLHelper.TABLE_RESOLD_HOUSE, self.city, region), (house, area, height, price))
        cur.close()

    def __calcRegionInfo(self, region):
        count = 0
        maxprice = 0
        total = 0
        cur = self.conn.cursor()
        cur.execute("SELECT PRICE FROM {}{}_{};".format(SQLHelper.TABLE_RESOLD_HOUSE, self.city, region))
        for info in cur.fetchall():
            if(info[0] > maxprice):
                maxprice = info[0]
            total += info[0]
            count += 1
        cur.close()
        return maxprice, total, count

    def insertNewHouse(self, region, house, area, height, price):
        info = self.__getRegionInfo(region)
        if(info):
            id = info[0]
        else:
            id = self.__insertRegion(region)
            self.__createRegionTable(id)
        self.__addNewHouseInfo(id, house, area, height, price)


    def autoUpdateHouseInfo(self):
        cur = self.conn.cursor()
        cur.execute("SELECT ID, REGION FROM {}{};".format(SQLHelper.TABLE_RESOLD_HOUSE, self.city))
        regionlist = cur.fetchall()
        cur.close()
        for info in regionlist:
            maxprice, total, count = self.__calcRegionInfo(info[0])
            self.__updateRegionInfo(info[0], info[1], maxprice, total, count)

    def close(self):
        self.conn.close()
        
    def clearData(self):
        cur = self.conn.cursor()
        cur.execute("SELECT ID FROM {}{};".format(SQLHelper.TABLE_RESOLD_HOUSE, self.city))
        for info in cur.fetchall():
            cur.execute("DROP TABLE {}{}_{};".format(SQLHelper.TABLE_RESOLD_HOUSE, self.city, info[0]))
        cur.execute("TRUNCATE TABLE {}{};".format(SQLHelper.TABLE_RESOLD_HOUSE, self.city))
        cur.close()

    def getAllRegionInfo(self):
        infolist = []
        cur = self.conn.cursor()
        cur.execute("SELECT REGION, LONGITUDE, LATITUDE, MAXPRICE, TOTAL, COUNT FROM {}{} WHERE LONGITUDE IS NOT null AND COUNT > 0;".format(SQLHelper.TABLE_RESOLD_HOUSE, self.city))
        for info in cur.fetchall():
            infolist.append({'region':info[0], 'lon':info[1], 'lat':info[2], 'max':info[3], 'total':info[4], 'count':info[5]})
        cur.close()
        return infolist

    def getHouseInfoFrom(self, index):
        infolist = []
        cur = self.conn.cursor()
        cur.execute(("SELECT HOUSE, AREA, HEIGHT, PRICE, LONGITUDE, LATITUDE FROM {}{}_{} WHERE LONGITUDE IS NOT null;").format(SQLHelper.TABLE_RESOLD_HOUSE, self.city, index))
        for info in cur.fetchall():
            infolist.append({'house':info[0], 'area':info[1], 'height':info[2], 'price':info[3], 'lon':info[4], 'lat':info[5]})
        cur.close()
        return infolist

    def getAllHouseInfoInRegion(self, region):
        houselist = []
        cur = self.conn.cursor()
        cur.execute(("SELECT ID, MAXPRICE, COUNT FROM {}{} WHERE REGION LIKE '%{}%';").format(SQLHelper.TABLE_RESOLD_HOUSE, self.city, region))
        values = cur.fetchall();
        cur.close()
        for value in values:
            houselist.append({'max':value[1], 'count':value[2], 'list':self.getHouseInfoFrom(value[0])})
        return houselist

